﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Windows;

namespace IISLogAnalyzer.Common
{
    public static class SqlConnectionExtension
    {
        /// <summary>
        /// 使用 SqlBulkCopy 向 destinationTableName 表插入数据
        /// </summary>
        /// <typeparam name="TModel">必须拥有与目标表所有字段对应属性</typeparam>
        /// <param name="conn"></param>
        /// <param name="modelList">要插入的数据</param>
        /// <param name="batchSize">SqlBulkCopy.BatchSize</param>
        /// <param name="destinationTableName">如果为 null，则使用 TModel 名称作为 destinationTableName</param>
        /// <param name="bulkCopyTimeout">SqlBulkCopy.BulkCopyTimeout</param>
        /// <param name="externalTransaction">要使用的事务</param>
        public static void BulkCopy(this SqlConnection conn, DataTable modelList, int batchSize, string destinationTableName = null, int? bulkCopyTimeout = null, SqlTransaction externalTransaction = null)
        {
            bool shouldCloseConnection = false;

            DataTable dtToWrite = modelList;

            SqlBulkCopy sbc = null;

            try
            {
                if (externalTransaction != null)
                    sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, externalTransaction);
                else
                    sbc = new SqlBulkCopy(conn);

                using (sbc)
                {
                    sbc.BatchSize = batchSize;
                    sbc.DestinationTableName = destinationTableName;

                    if (bulkCopyTimeout != null)
                        sbc.BulkCopyTimeout = bulkCopyTimeout.Value;

                    if (conn.State != ConnectionState.Open)
                    {
                        shouldCloseConnection = true;
                        conn.Open();
                    }

                    sbc.WriteToServer(dtToWrite);
                }
            }
            finally
            {
                if (shouldCloseConnection && conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }

        //public static DataTable ToSqlBulkCopyDataTable<TModel>(List<TModel> modelList, SqlConnection conn, string tableName)
        //{
        //    DataTable dt = new DataTable();

        //    Type modelType = typeof(TModel);

        //    List<SysColumn> columns = GetTableColumns(conn, tableName);
        //    List<PropertyInfo> mappingProps = new List<PropertyInfo>();

        //    var props = modelType.GetProperties();
        //    for (int i = 0; i < columns.Count; i++)
        //    {
        //        var column = columns[i];
        //        PropertyInfo mappingProp = props.Where(a => a.Name == column.Name).FirstOrDefault();
        //        if (mappingProp == null)
        //            throw new Exception(string.Format("model 类型 '{0}'未定义与表 '{1}' 列名为 '{2}' 映射的属性", modelType.FullName, tableName, column.Name));

        //        mappingProps.Add(mappingProp);
        //        Type dataType = GetUnderlyingType(mappingProp.PropertyType);
        //        if (dataType.IsEnum)
        //            dataType = typeof(int);
        //        dt.Columns.Add(new DataColumn(column.Name, dataType));
        //    }

        //    foreach (var model in modelList)
        //    {
        //        DataRow dr = dt.NewRow();
        //        for (int i = 0; i < mappingProps.Count; i++)
        //        {
        //            PropertyInfo prop = mappingProps[i];
        //            object value = prop.GetValue(model);

        //            if (GetUnderlyingType(prop.PropertyType).IsEnum)
        //            {
        //                if (value != null)
        //                    value = (int)value;
        //            }

        //            dr[i] = value ?? DBNull.Value;
        //        }

        //        dt.Rows.Add(dr);
        //    }

        //    return dt;
        //}
        //static List<SysColumn> GetTableColumns(SqlConnection sourceConn, string tableName)
        //{
        //    string sql = string.Format("select * from syscolumns inner join sysobjects on syscolumns.id=sysobjects.id where sysobjects.xtype='U' and sysobjects.name='{0}' order by syscolumns.colid asc", tableName);

        //    List<SysColumn> columns = new List<SysColumn>();
        //    using (SqlConnection conn = (SqlConnection)((ICloneable)sourceConn).Clone())
        //    {
        //        conn.Open();
        //        using (var reader = conn.ExecuteReader(sql))
        //        {
        //            while (reader.Read())
        //            {
        //                SysColumn column = new SysColumn();
        //                column.Name = reader.GetDbValue("name");
        //                column.ColOrder = reader.GetDbValue("colorder");

        //                columns.Add(column);
        //            }
        //        }
        //        conn.Close();
        //    }

        //    return columns;
        //}

        static Type GetUnderlyingType(Type type)
        {
            Type unType = Nullable.GetUnderlyingType(type); ;
            if (unType == null)
                unType = type;

            return unType;
        }

        class SysColumn
        {
            public string Name { get; set; }
            public int ColOrder { get; set; }
        }

        #region MyRegion


        /// <summary>
        /// 将DataTable写入数据库的表中
        /// </summary>
        /// <param name="source">数据源DataTable</param>
        /// <param name="tableName">数据目标的表名</param>
        /// <param name="useTransaction">操作过程是否使用事务</param>
        /// <param name="databaseConnString">数据库连接字符串</param>
        /// <param name="dropTable">删除DB中已存在的表(并自动新建表)</param>
        /// <param name="primaryKeys">主键的列名</param>
        public static void WriteToDataBase(DataTable source, string tableName, bool useTransaction, bool dropTable, string[] primaryKeys)
        {
            string databaseConnString = SqlHelper.connStr;
            //判断表是否存在 
            //dataHelper.IsConnString = true; //使用数据库连接字符串创建sqlserver操作对象 
            string sql = "select * from sys.objects where type='U' and name='" + tableName + "'";
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            if (dt.Rows.Count > 0)
            {
                if (dropTable == true)
                {
                    sql = "drop table " + tableName + "";   //清除已存在的表
                    SqlHelper.ExecuteNonQuery(sql);
                }
                else
                {
                    SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(databaseConnString, SqlBulkCopyOptions.UseInternalTransaction | SqlBulkCopyOptions.FireTriggers);
                    sqlbulkcopy.DestinationTableName = tableName;//数据库中的表名

                    sqlbulkcopy.WriteToServer(source);
                    return;
                }
            }
            CreateTable(source.Columns, tableName, primaryKeys);
            var sqlBulkCopy = new System.Data.SqlClient.SqlBulkCopy(databaseConnString, SqlBulkCopyOptions.FireTriggers);//启动触发器
            if (useTransaction == true)
            {
                sqlBulkCopy = new System.Data.SqlClient.SqlBulkCopy(databaseConnString, SqlBulkCopyOptions.UseInternalTransaction | SqlBulkCopyOptions.FireTriggers); //导入的数据在一个事务中 
            }
            sqlBulkCopy.DestinationTableName = tableName;
            foreach (DataColumn c in source.Columns)
            {
                sqlBulkCopy.ColumnMappings.Add(c.ColumnName, c.ColumnName);
            }
            //SqlBulkCopy.BulkCopyTimeout = this.timeout;  //超时时间 
            sqlBulkCopy.BatchSize = 3000;  //每次传输3000行 
            sqlBulkCopy.WriteToServer(source);
        }

        /// <summary>
        /// 创建表以及触发器
        /// </summary>
        /// <param name="columns">列名</param>
        /// <param name="tableName">表名</param>
        /// <param name="primaryKeys">主键的列名</param>
        public static void CreateTable(System.Data.DataColumnCollection columns, string tableName, string[] primaryKeys)
        {
            if (primaryKeys == null || primaryKeys.Length < 1)
            {
                MessageBox.Show("主键不允许为空!");
                return;
            }
            StringBuilder sb = new StringBuilder();
            // sb.Append("create table [" + tableName + "] (autoId  int identity(1,1),");
            sb.Append("create table [" + tableName + "] (");
            foreach (DataColumn column in columns)
            {
                sb.Append(" [" + column.ColumnName + "] " + GetTableColumnType(column.DataType) + ",");
            }

            string sql = sb.ToString();
            sql = sql.TrimEnd(',');
            sql += ")";

            sb.Clear();
            var temp1 = primaryKeys;
            for (int i = 0; i < primaryKeys.Length; i++)
            {
                temp1[i] = tableName + "." + primaryKeys[i] + "=Inserted." + primaryKeys[i];
            }
            List<string> temp2 = new List<string>();
            for (int i = 0; i < columns.Count; i++)
            {
                temp2.Add("[" + columns[i].ColumnName + "]=Inserted.[" + columns[i].ColumnName+"]");
            }
            List<string> temp3 = new List<string>();
            for (int i = 0; i < columns.Count; i++)
            {
                temp3.Add(columns[i].ColumnName);
            }
            sb.Append("CREATE TRIGGER [tri_" + tableName + "_edit]  ON [" + tableName + "] instead of insert as");
            sb.Append("  IF EXISTS (");
            sb.Append("SELECT * FROM    " + tableName + ",Inserted WHERE " + String.Join(" AND ", temp1) + ")");
            sb.Append(" UPDATE [" + tableName + "] SET " + string.Join(",", temp2) + " FROM [" + tableName + "] JOIN inserted ON " + String.Join(" AND ", temp1) + " ");
            sb.Append(" ELSE ");

            sb.Append(" INSERT  [" + tableName + "]([" + string.Join("],[", temp3) + "]) SELECT  [" + string.Join("],[", temp3) + "] FROM inserted  ");
            // sql = sql + " ; " + sb.ToString(); 
            SqlHelper.ExecuteNonQuery(sql);
            SqlHelper.ExecuteNonQuery(sb.ToString());
        }

        private static string GetTableColumnType(System.Type type)
        {
            string result = "nvarchar(2000)";
            string sDbType = type.ToString();
            switch (sDbType)
            {
                case "System.String":
                    break;
                case "System.Int16":
                    result = "int";
                    break;
                case "System.Int32":
                    result = "int";
                    break;
                case "System.Int64":
                    result = "float";
                    break;
                case "System.Decimal":
                    result = "decimal(18,4)";
                    break;
                case "System.Double":
                    result = "decimal(18,4)";
                    break;
                case "System.DateTime":
                    result = "datetime";
                    break;
                default:
                    break;
            }
            return result;
        }

        #endregion
    }
}